﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using System.Diagnostics;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Reflection;

namespace InfoPick.Lib.Tools
{
    public class ExcelTools
    {
        /// <summary>
        /// 根据句柄获取进程id
        /// </summary>
        /// <param name="hwnd"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="path"></param>
        /// <param name="ddfList"></param>
        /// <param name="odfList"></param>
        /// <param name="gsList"></param>
        public static void Export(string path, List<GMDDF> ddfList,List<GMODF> odfList,List<GSDDF> gsList,string []sheetNames)
        {
            Microsoft.Office.Interop.Excel.Application excelApp = null;
            try
            {
                //创建Excel
                excelApp = new Microsoft.Office.Interop.Excel.Application();

                //创建工作簿（WorkBook：即Excel文件主体本身）
                Workbook excelWB = excelApp.Workbooks.Add(System.Type.Missing);
                for (int i = excelWB.Worksheets.Count; i < 3; i++)
                {
                    excelWB.Worksheets.Add();
                }


                Worksheet excelWS = (Worksheet)excelWB.Worksheets[1];
                excelWS.Name = sheetNames[0];
                createDDFSheet(excelWS, ddfList);

                excelWS = (Worksheet)excelWB.Worksheets[2];
                excelWS.Name = sheetNames[1];
                createGSSheet(excelWS, gsList);

                excelWS = (Worksheet)excelWB.Worksheets[3];
                excelWS.Name = sheetNames[2];
                createODFSheet(excelWS, odfList);

                
                //将其进行保存到指定的路径
                excelWB.SaveAs(path, 18);
                excelWB.Close();
            }
            finally
            {
                //释放进程
                KillAllExcel(excelApp);
            }
        }


        private static bool KillAllExcel(Microsoft.Office.Interop.Excel.Application excelApp)
        {
            try
            {
                if (excelApp != null)
                {
                    //获取该excel进程的pid
                    int excelProcessId = 0;
                    GetWindowThreadProcessId(new IntPtr(excelApp.Hwnd), out excelProcessId);   // convert pointer to process id

                    excelApp.Quit();

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    //释放COM组件，其实就是将其引用计数减1   
                    //System.Diagnostics.Process theProc;   

                    if (excelProcessId != 0)
                    {
                        //这里不能直接通过进程id查找，因为前面调用了quit函数，
                        //进程有可能已经正常关闭，如果再用进程ID查找不存在的进程会出现异常
                        var proc = System.Diagnostics.Process.GetProcessesByName("EXCEL")
                            .SingleOrDefault(c => c.Id == excelProcessId);
                        //先关闭图形窗口。如果关闭失败.有的时候在状态里看不到图形窗口的excel了，   
                        //但是在进程里仍然有EXCEL.EXE的进程存在，那么就需要释放它
                        if (proc != null && proc.CloseMainWindow() == false)
                        {
                            proc.Kill();
                        }
                    }
                    excelApp = null;
                    return true;
                }
            }
            catch (Exception e)
            {
                throw new Exception("释放excel资源出错！请保存现有excel应用后在任务管理器中手动停止EXCEL进程！", e);
            }
            return true;
        }


        private static void createDDFSheet(Worksheet excelWS, List<GMDDF> ddfList)
        {
            Range range = null;
            #region 一级表头
            
            range = (Range)excelWS.get_Range("A1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("B1", "I1");
            setRangeFormat(range, "交换机侧", false, false);

            range = (Range)excelWS.get_Range("J1");
            setRangeFormat(range, "交换DDF", false, false);

            range = (Range)excelWS.get_Range("K1", "L1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("M1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("N1", "U1");
            setRangeFormat(range, "华为软交换设备数据配置使用", true, false);
            #endregion


            #region 二级表头
            string[] headers = new string[]{"序号","机柜名称","框号","板组号","槽位号","端口号","全局号","全局TID",
                                        "No.7链路时隙","架号-板面号-板号-端子号","系统","方向","备注","BSG模块号",
                                        "SPF模块号","扣板号","CCU模块号","起始电路号(SC)","终止电路号(EC)","START_CIC","START_TID"};
            char colStart = 'A';
            for (int i = 0; i < headers.Length; i++)
            {
                range = (Range)excelWS.get_Range(string.Format("{0}2", (char)((int)colStart + i)));
                setRangeFormat(range, headers[i]);
                range.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);

            }


            #endregion

            //excelWS.Cells.NumberFormat = "@";     
            //如果数据中存在数字类型 可以让它变文本格式显示

            int startRow = 3;

            #region 填入数据
            //get values
            object[,] objectArray = new object[ddfList.Count, headers.Length];

            for (int i = 0; i < ddfList.Count; i++)
            {
                objectArray[i, 0] = ddfList[i].Id;
                objectArray[i, 1] = ddfList[i].Name;
                objectArray[i, 2] = ddfList[i].Frame;
                objectArray[i, 3] = ddfList[i].BoardNum;
                objectArray[i, 4] = ddfList[i].SLotNum;
                objectArray[i, 5] = ddfList[i].Port;
                objectArray[i, 6] = ddfList[i].GlobalNum;
                objectArray[i, 7] = ddfList[i].GlobalTid;
                objectArray[i, 8] = ddfList[i].No7Link;
                objectArray[i, 9] = ddfList[i].GroupNum;
                objectArray[i, 10] = ddfList[i].System;
                objectArray[i, 11] = ddfList[i].Direction;
                objectArray[i, 12] = ddfList[i].Remark;
                objectArray[i, 13] = ddfList[i].BSGNum;
                objectArray[i, 14] = ddfList[i].SPFNum;
                objectArray[i, 15] = ddfList[i].KNum;
                objectArray[i, 16] = ddfList[i].CCUNum;
                objectArray[i, 17] = ddfList[i].SCNum;
                objectArray[i, 18] = ddfList[i].ECNum;
                objectArray[i, 19] = ddfList[i].StartCic;
                objectArray[i, 20] = ddfList[i].StartTid;
            }
            #endregion

            #region 调整样式
            range = (Range)excelWS.get_Range("A" + startRow.ToString(), ((char)((int)colStart + headers.Length - 1)).ToString() + (startRow + ddfList.Count - 1).ToString());

            range.Application.ScreenUpdating = false;
            range.Application.EnableEvents = false;

            range.Value2 = objectArray;

            range.Application.ScreenUpdating = true;
            range.Application.EnableEvents = true;

            range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,
              System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));

            range = (Range)excelWS.get_Range("A1", ((char)((int)colStart + headers.Length - 1)).ToString() + (startRow + ddfList.Count - 1).ToString());
            
            range.Columns.AutoFit();
            #endregion

            #region 固定表头和列
            //excelWS.Application.ActiveWindow.SplitRow = 2;
            //excelWS.Application.ActiveWindow.SplitColumn = 2;
            //excelWS.Application.ActiveWindow.FreezePanes = true;
            #endregion
        }

        private static void createODFSheet(Worksheet excelWS, List<GMODF> odfList)
        {
            Range range = null;
            #region 一级表头
            range = (Range)excelWS.get_Range("A1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("B1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("C1", "J1");
            setRangeFormat(range, "交换机侧", false, false);


            range = (Range)excelWS.get_Range("K1", "M1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("N1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("O1");
            setRangeFormat(range, "", false);

            range = (Range)excelWS.get_Range("P1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("Q1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("R1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("S1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("T1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("U1");
            setRangeFormat(range, "", false, false);
            #endregion
           
            #region 二级表头
            string[] headers = new string[]{"序号","机柜名称","框号","板组号","槽位号","主备状态","端口号","全局号","全局TID",
                                        "ODF位置","No.7链路时隙","系统","方向","备注",
                                        "SPF模块号","扣板号","CCU模块号","起始电路号(SC)","终止电路号(EC)","START_CIC","START_TID"};
            
            char colStart = 'A';
            for (int i = 0; i < headers.Length; i++)
            {
                range = (Range)excelWS.get_Range(string.Format("{0}2", (char)((int)colStart + i)));
                setRangeFormat(range, headers[i]);
                range.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
            }


            #endregion

            //excelWS.Cells.NumberFormat = "@";     
            //如果数据中存在数字类型 可以让它变文本格式显示

            int startRow = 3;

            #region 填入数据

            object[,] objectArray = new object[odfList.Count, headers.Length];
            for (int i = 0; i < odfList.Count; i++)
            {
                objectArray[i, 0] = odfList[i].Id;
                objectArray[i, 1] = odfList[i].Name;
                objectArray[i, 2] = odfList[i].Frame;
                objectArray[i, 3] = odfList[i].BoardNum;
                objectArray[i, 4] = odfList[i].SLotNumStr;
                objectArray[i, 5] = odfList[i].IsPrimaryStr;
                objectArray[i, 6] = odfList[i].Port;
                objectArray[i, 7] = odfList[i].GlobalNum;
                objectArray[i, 8] = odfList[i].GlobalTid;
                objectArray[i, 9] = odfList[i].ODFPosition;
                objectArray[i, 10] = odfList[i].No7Link;
                objectArray[i, 11] = odfList[i].System;
                objectArray[i, 12] = odfList[i].Direction;
                objectArray[i, 13] = odfList[i].Remark;
                objectArray[i, 14] = odfList[i].SPFNum;
                objectArray[i, 15] = odfList[i].KNum;
                objectArray[i, 16] = odfList[i].CCUNum;
                objectArray[i, 17] = odfList[i].SCNum;
                objectArray[i, 18] = odfList[i].ECNum;
                objectArray[i, 19] = odfList[i].StartCic;
                objectArray[i, 20] = odfList[i].StartTid;
            }
            #endregion

            #region 调整样式
            range = (Range)excelWS.get_Range("A" + startRow.ToString(), ((char)((int)colStart + headers.Length - 1)).ToString() + (startRow + odfList.Count - 1).ToString());

            range.Application.ScreenUpdating = false;
            range.Application.EnableEvents = false;

            range.Value2 = objectArray;

            range.Application.ScreenUpdating = true;
            range.Application.EnableEvents = true;

            range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,
              System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));

            range = (Range)excelWS.get_Range("A1", ((char)((int)colStart + headers.Length - 1)).ToString() + (startRow + odfList.Count - 1).ToString());
            range.Columns.AutoFit();
            #endregion

        }

        private static void createGSSheet(Worksheet excelWS, List<GSDDF> gsList)
        {
            Range range = null;
            #region 一级表头
            range = (Range)excelWS.get_Range("A1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("B1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("C1", "G1");
            setRangeFormat(range, "交换机侧", false, false);

            range = (Range)excelWS.get_Range("H1");
            setRangeFormat(range, "", false, false);
            
            range = (Range)excelWS.get_Range("I1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("J1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("K1");
            setRangeFormat(range, "", false, false);

            range = (Range)excelWS.get_Range("L1");
            setRangeFormat(range, "", false, false);
            #endregion

            #region 二级表头
            string[] headers = new string[]{"序号","机柜名称","框号","槽位号","端口号","链路类型",
                                        "No.7链路时隙","架号-板面号-板号-端子号","系统","方向","备注","CSU模块号"};

            char colStart = 'A';
            for (int i = 0; i < headers.Length; i++)
            {
                range = (Range)excelWS.get_Range(string.Format("{0}2", (char)((int)colStart + i)));
                setRangeFormat(range, headers[i]);
                range.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
            }


            #endregion

            //excelWS.Cells.NumberFormat = "@";     
            //如果数据中存在数字类型 可以让它变文本格式显示

            int startRow = 3;

            #region 填入数据
            object[,] objectArray = new object[gsList.Count, headers.Length];
            for (int i = 0; i < gsList.Count; i++)
            {
                objectArray[i, 0] = gsList[i].Id;
                objectArray[i, 1] = gsList[i].Name;
                objectArray[i, 2] = gsList[i].Frame;
                objectArray[i, 3] = gsList[i].SLotNum;
                objectArray[i, 4] = gsList[i].Port;
                objectArray[i, 5] = gsList[i].LinkType;
                objectArray[i, 6] = gsList[i].No7Link;
                objectArray[i, 7] = gsList[i].GroupNum;
                objectArray[i, 8] = gsList[i].System;
                objectArray[i, 9] = gsList[i].Direction;
                objectArray[i, 10] = gsList[i].Remark;
                objectArray[i, 11] = gsList[i].CSUNum;
            }
            #endregion

            #region 调整样式
            range = (Range)excelWS.get_Range("A" + startRow.ToString(), ((char)((int)colStart + headers.Length - 1)).ToString() + (startRow + gsList.Count - 1).ToString());

            range.Application.ScreenUpdating = false;
            range.Application.EnableEvents = false;

            range.Value2 = objectArray;

            range.Application.ScreenUpdating = true;
            range.Application.EnableEvents = true;

            range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,
            System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));

            range = (Range)excelWS.get_Range("A1", ((char)((int)colStart + headers.Length - 1)).ToString() + (startRow + gsList.Count - 1).ToString());
            range.Columns.AutoFit();
            #endregion

        }


        private static void setRangeFormat(Range range,string value,bool bold=true, bool topBorder = true)
        {
            range.Merge(0);
            range.Font.Size = 10;
            range.Font.Underline = false;
            range.Font.Name = "宋体";
            range.Font.Bold = bold;
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.Borders.LineStyle = 1;
            range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,
               System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));
            if (!topBorder)
                range.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlLineStyleNone;
            range.Value = value;
        }


        public static List<string> GetSheetNames(string fullPath)
        {
            if (File.Exists(fullPath))
            {
                Microsoft.Office.Interop.Excel.Application excel = null;

                try
                {
                    excel = new Microsoft.Office.Interop.Excel.Application();

                    Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(fullPath);
                    
                    List<string> sheetname = new List<string>();

                    foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in wb.Sheets)
                    {
                        sheetname.Add(sheet.Name);
                    }
                    return sheetname;
                }
               
                finally
                {
                    KillAllExcel(excel);
                }
            }
            else
            {
                throw new Exception(string.Format("文件：{0}不存在！", fullPath));
            }
        }

        
        public static DataSet ReadBaseData(string fullPath)
        {
            //复制文件，因为excel如果被打开，不能通过oledb方式读取
            FileInfo fi = new FileInfo(fullPath);
            string newFile = string.Format(@"{0}{1}{2}",Global.TelnetTempDir, Guid.NewGuid().ToString(), fi.Extension);
            FileInfo newfi = new FileInfo(newFile);
            if (!newfi.Directory.Exists)
                Directory.CreateDirectory(newfi.DirectoryName);
            fi.CopyTo(newFile);

            var sheets = GetSheetNames(new FileInfo(newFile).FullName);
            if (sheets.Count < 3)
                throw new Exception("excel文件中sheet数少于3！");
            DataSet ds = readData(newFile, sheets.Take(3).ToArray());

            //删除复制文件
            if (File.Exists(newFile))
                File.Delete(newFile);

            var mappings = ExcelMapping.GetGMDDFMapping();
            getActualTable(ds.Tables[0], mappings);
            mappings = ExcelMapping.GetGSDDFMapping();
            getActualTable(ds.Tables[1], mappings);
            mappings = ExcelMapping.GetGMODFMapping();
            getActualTable(ds.Tables[2], mappings);

            return ds;
        }

        /// <summary>
        /// 查找真实的table
        /// 取出多余的表头和空行
        /// </summary>
        /// <param name="table"></param>
        /// <param name="mappings"></param>
        /// <returns></returns>
        private static void getActualTable(System.Data.DataTable table, Dictionary<string,string> mappings)
        {
            var values = mappings.Select(c => c.Value);

            //包含所有关键列（values）的表头行号
            int ?headerRow = null;
            //数据行号
            int ?dataRow = null;
            for (int iRow=0; iRow < table.Rows.Count; iRow++)
            {
                var items = table.Rows[iRow].ItemArray;
                if (headerRow == null)
                {
                    if (items.Where(c => values.Contains(c.ToString())).Count() == values.Count())
                    {
                        //将最低一级的表头赋给datatable表头
                        for (int colIdx = 0; colIdx < table.Columns.Count; colIdx++)
                        {
                            if (!string.IsNullOrEmpty(items[colIdx].ToString()))
                                table.Columns[colIdx].ColumnName = items[colIdx].ToString();
                        }
                        headerRow = iRow;
                    }
                }
                else
                {
                    int tmp = 0;
                    if (int.TryParse(items[0].ToString(), out tmp))
                    {
                        if (tmp == 1)
                        {
                            dataRow = iRow;
                            break;
                        }
                    }
                }
            }
            //没有找到表头或者数据
            if (headerRow == null || dataRow == null)
            {
                table.Rows.Clear();
                return;
            }
            //删除多余的表头
            for (int i = 0; i < dataRow; i++)
            {
                table.Rows.RemoveAt(0);
                headerRow--;
            }
            //删除空行
            var emptyRows = table.AsEnumerable().Where(c=>string.IsNullOrEmpty(string.Join("",c.ItemArray).Trim())).ToArray();
            for (int i = 0; i < emptyRows.Length; i++)
            {
                table.Rows.Remove(emptyRows[i]);
            }
            //格式化数据
            foreach (var row in table.AsEnumerable())
            {
                string frame = row[mappings["Frame"]].ToString();
                string sub = StringTools.AdvSubString(frame, "(", ")",0);
                if (!string.IsNullOrEmpty(sub))
                {
                    row[mappings["Frame"]] = sub;
                    continue;
                }
                sub = StringTools.AdvSubString(frame, "（", "）", 0);
                if (!string.IsNullOrEmpty(sub))
                {
                    row[mappings["Frame"]] = sub;
                    continue;
                }
            }
        }

        private static System.Data.DataSet readData(string fullPath, string[] sheetNames)
        {
            string strConn = string.Empty;
            if (Path.GetExtension(fullPath).Equals(".xls"))//for 97-03 Excel file
            {
                strConn = string.Format(@"Provider=Microsoft.Jet.OLEDB.{0};Data Source='{1}';Extended Properties='Excel {2};HDR={3};IMEX=1';", "4.0", fullPath, "8.0", "Yes");
            }
            else if (Path.GetExtension(fullPath).Equals(".xlsx"))  //for 2007 Excel file
            {
                strConn = string.Format(@"Provider=Microsoft.ACE.OLEDB.{0};Data Source='{1}';Extended Properties='Excel {2};HDR={3};IMEX=1';", "12.0", fullPath, "12.0", "Yes");
            }
            else
            {
                throw new Exception("excel文件必须为 xls 或者 xlsx");
            }

            //不sleep读取可能报错，原因未知，暂时处理
            System.Threading.Thread.Sleep(1000);
            OleDbDataAdapter ada = new OleDbDataAdapter();
            ada.SelectCommand = new OleDbCommand();
            ada.SelectCommand.Connection = new OleDbConnection(strConn);
            System.Data.DataSet ds = new DataSet();
            foreach (var sheet in sheetNames)
            {
                ada.SelectCommand.CommandText = string.Format(@"select * from [{0}$]", sheet);

                System.Data.DataTable dt = new System.Data.DataTable(sheet);

                ada.Fill(dt);

                ds.Tables.Add(dt);
            }
            
            return ds;

        }
    }
}
